Release 10.1A: OpenEdge Data Management:
SQL Development

Understanding lock acquisition

Knowing which objects get locked and when goes a long way towards helping you develop applications that are more robust and predictable. SQL uses the transaction isolation level exclusively to determine what lock mode is applied to which objects. Understanding how this translates into object locks and lock modes is key to communicating your application’s intentions to the SQL engine.

The strongest locks are held when the transaction isolation level is SERIALIZABLE, and the weakest locks are held when the transaction isolation level is READ UNCOMMITTED. This also translates into application concurrency—the higher the transaction isolation level, the less concurrent your application will be.

Information schema locks

Every operation performed by the OpenEdge SQL Engine operates inside a transaction.

For each transaction, an information schema share-lock is acquired at the beginning of the transaction and released at the end of the transaction. This is true whether a transaction is committed successfully or terminated abnormally. Acquiring the information schema share-lock protects the information schema from being altered while the transaction is active.

During the life of an active connection, many transactions can be performed. The first transaction begins upon connection to the SQL engine and is used to read the information schema. Once the information schema has been read, the transaction ends. Each successive operation will then begin and end a transaction requiring, at a minimum, a share-lock on the information schema.

While the connection is quiet, there is no active transaction and therefore no lock held on the information schema. If an operation is being performed that will modify the information schema, an exclusive lock on the information schema will be requested. For the exclusive lock on the information schema to be granted, there can be no other active transactions in the database. Once granted, the information schema lock is upgraded from a share to an exclusive lock. While this transaction is active, the exclusive lock prohibits other transactions.

The lock on information schema supersedes all locks on tables and records via transaction isolation level settings for data manipulation operations.

Table and record locks

To get a record lock of sufficient strength for an operation, you must first have a table lock of sufficient strength. Regardless of the current transaction isolation level, if the application’s intent is to perform an operation other than a fetch, the lock mode is strengthened for the operation.

With the exception of the READ UNCOMMITTED isolation level, you are not prohibited from creating or updating records based on the transaction isolation level. It is the responsibility of the RDBMS to provide sufficient lock escalation when an operation is being performed that requires lock upgrades. Note that you are prohibited from creating or updating records when the isolation level is READ UNCOMMITTED.